package com.liuxl.db.tool;

import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import com.github.pagehelper.Page;
import com.github.pagehelper.parser.OrderByParser;
import com.github.pagehelper.parser.SqlServerParser;
import com.google.common.collect.Lists;
import com.liuxl.db.JdbcUtils;
import com.liuxl.db.bo.Column;

import cn.hutool.core.util.StrUtil;

public class SQLServerDatabaseTool extends AbstractDatabaseTool {
	
	
	@Override
	public List<String> getTableNames(DataSource dataSource) throws SQLException {
		List<String> tableNames = Lists.newArrayList();
		String sql = "select table_name from information_schema.TABLES";
		List<Map<String, Object>> datas = JdbcUtils.executeQuery(dataSource, sql,Lists.newArrayList());
		for (Map<String, Object> data : datas) {
			String tableName = data.get("table_name") == null ? "" : data.get("table_name").toString();
			tableNames.add(tableName);
		}
		return tableNames;
	}
	
	@Override
	public List<String> getColumnNames(DataSource dataSource, String tableName) throws SQLException {
		List<String> columnNames = Lists.newArrayList();
		String sql = "select column_name from information_schema.columns where  table_name = ?";
		List<Map<String, Object>> datas = JdbcUtils.executeQuery(dataSource, sql,tableName);
		for (Map<String, Object> data : datas) {
			String columnName = data.get("column_name") == null ? "" : data.get("column_name").toString();
			columnNames.add(columnName);
		}
		return columnNames;
	}

	@Override
	public String getPageSql(Page<Map<String,Object>> page, String sql) {
		String orderBy = page.getOrderBy();
		if (StrUtil.isNotBlank(orderBy)) {
			sql = OrderByParser.converToOrderBySql(sql, orderBy);
		}
        sql = replaceSql.replace(sql);
        SqlServerParser parser = new SqlServerParser();
        sql = parser.convertToPageSql(sql, Integer.getInteger(String.valueOf(page.getStartRow()), 0), page.getPageSize());
        sql = replaceSql.restore(sql);
		return sql;
	}
	
	/**
	 * 注意：OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY 仅支持2008以上版本且必须排序 
	 */
	@Override
	public String getPageSql(String sql) {
        return null;
	}
	
	@Override
	public String getCountSql(String sql) {
        sql = replaceSql.replace(sql);
        sql = super.getCountSql(sql);
        sql = replaceSql.restore(sql);
		return sql;
	}

	@Override
	public void addPageParameter(Page<Map<String,Object>> page, List<Object> parameters) {
	}

	@Override
	public boolean existsTableName(DataSource dataSource, String tableName) throws SQLException {
		String sql = "select count(1) from information_schema.TABLES where table_name = ? ";
		List<Object[]> datas = JdbcUtils.executeQuery2(dataSource, sql,tableName);
		Object obj = datas.get(0)[0];
		Integer count = obj == null ? 0 : Integer.valueOf(obj.toString());
		return count != 0;
	}

	@Override
	public boolean existsColumnName(DataSource dataSource, String tableName, String columnName) throws SQLException {
		String sql = "select count(1) from information_schema.columns where  table_name = ? and column_name =? ";
		List<Object[]> datas = JdbcUtils.executeQuery2(dataSource, sql,tableName,columnName);
		Object obj = datas.get(0)[0];
		Integer count = obj == null ? 0 : Integer.valueOf(obj.toString());
		return count != 0;
	}
	
	@Override
	public void modifyColumn(DataSource dataSource, String tableName, Column column) throws SQLException {
		String name = column.getName();
		Boolean isNullable = column.getIsNullable();
		String columnType = column.bulidColumnType();
    	String sql = "alter table "+tableName+" alter column "+name+" "+ columnType+" "+(isNullable ? "" : " not null ");
    	JdbcUtils.execute(dataSource, sql);
	}

}
